﻿Public Class proveedores

    Dim cadena As String = "Data Source=CESAR-PC\SQLSERVER;Initial Catalog=CAPCrepuestos;Integrated Security=True"
    Enum termino
        aprobado
        rechazado
    End Enum
    Dim accion As estado = estado.insertar

    Enum estado
        insertar
        modificar
    End Enum

    Private Sub cargarCombo(ByVal combo As ComboBox, ByRef datos As DataTable, ByVal pk As String, ByVal descripcion As String)

        combo.DataSource = datos
        combo.DisplayMember = descripcion
        combo.ValueMember = pk

    End Sub

    Private Function leo_tabla(ByVal nombreTabla As String) As Data.DataTable

        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion

        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM " & nombreTabla
        tabla.Load(cmd.ExecuteReader())

        conexion.Close()

        Return tabla

    End Function

    Private Sub btnNuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNuevo.Click

        txtApellidoContacto.Text = ""
        txtBarrio.Text = ""
        txtCelular.Text = ""
        txtCUIT.Text = ""
        txtDireccion.Text = ""
        txtFax.Text = ""
        txtFechaAlta.Text = ""
        txtHorario.Text = ""
        txtLocalidad.Text = ""
        txtMail.Text = ""
        txtMontoMinimo.Text = 0
        txtNombreContacto.Text = ""
        txtNombreProveedor.Text = ""
        txtRazonSocial.Text = ""
        txtTelefono.Text = ""
        cmbTipoFactura.SelectedValue = -1

        txtNumero.Text = cargarMayor()
        txtApellidoContacto.Enabled = True
        txtBarrio.Enabled = True
        txtCelular.Enabled = True
        txtCUIT.Enabled = True
        txtDireccion.Enabled = True
        txtFax.Enabled = True
        txtFechaAlta.Enabled = True
        txtFechaAlta.Text = Today
        txtHorario.Enabled = True
        txtLocalidad.Enabled = True
        txtMail.Enabled = True
        txtMontoMinimo.Enabled = True
        txtNombreContacto.Enabled = True
        txtNumero.Enabled = False
        txtNombreProveedor.Enabled = True

        txtRazonSocial.Enabled = True
        txtTelefono.Enabled = True
        cmbTipoFactura.Enabled = True
        ckbTransporte.Enabled = True
        txtMontoMinimo.Enabled = True
        btnEditar.Enabled = False
    End Sub

    Private Sub proveedores_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        cargarCombo(cmbTipoFactura, leo_tabla("tipo_factura"), "id", "descripcion")

        txtBarrio.Enabled = False
        txtDireccion.Enabled = False
        txtFax.Enabled = False
        txtLocalidad.Enabled = False
        txtMail.Enabled = False
        txtNumero.Enabled = False
        txtRazonSocial.Enabled = True
        txtTelefono.Enabled = False
        txtCUIT.Enabled = False
        txtFechaAlta.Enabled = False
        txtMontoMinimo.Enabled = False
        cmbTipoFactura.Enabled = False
        txtHorario.Enabled = False
        ckbTransporte.Enabled = False
        txtMontoMinimo.Enabled = False
        txtApellidoContacto.Enabled = False
        txtNombreContacto.Enabled = False
        txtCelular.Enabled = False
        txtNombreProveedor.Enabled = False


    End Sub

    Private Sub btnGuardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardar.Click
        If Me.accion = estado.insertar Then
            insertar()
        Else
            modificar()
        End If
    End Sub

    Private Function insertar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim consulta As String = ""
        Dim transporte As Integer

        If ckbTransporte.Checked = True Then
            transporte = 1
        Else
            transporte = 0
        End If
        conexion.ConnectionString = cadena
        conexion.Open()

        consulta = "insert into Proveedores (nombre_proveedor,numero,fecha_alta,razon_social,CUIT,telefono,direccion,fax,barrio,mail,localidad,monto_minimo,tipo_factura,nombre_contacto,apellido_contacto,celular_contacto,es_transporte)"
        consulta &= "values ('" & txtNombreProveedor.Text & "'"
        consulta &= ", " & Me.txtNumero.Text
        consulta &= ", '" & Me.txtFechaAlta.Text & "'"
        consulta &= ", '" & Me.txtRazonSocial.Text & "'"
        consulta &= ", '" & Me.txtCUIT.Text & "'"
        consulta &= ", '" & Me.txtTelefono.Text & "'"
        consulta &= ", '" & Me.txtDireccion.Text & "'"
        consulta &= ", '" & Me.txtFax.Text & "'"
        consulta &= ", '" & Me.txtBarrio.Text & "'"
        consulta &= ", '" & Me.txtMail.Text & "'"
        consulta &= ", '" & Me.txtLocalidad.Text & "'"
        consulta &= ", " & Me.txtMontoMinimo.Text 
        consulta &= ", " & Me.cmbTipoFactura.SelectedValue
        consulta &= ", '" & Me.txtNombreContacto.Text & "'"
        consulta &= ", '" & Me.txtApellidoContacto.Text & "'"
        consulta &= ", '" & Me.txtCelular.Text & "'"
        consulta &= ", " & transporte & ")"


        cmd.CommandType = CommandType.Text
        cmd.CommandText = consulta
        cmd.Connection = conexion
        cmd.ExecuteNonQuery()
        conexion.Close()
        MsgBox("El Proveedor se guardo exitosamente", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "ATENCION")
        Return termino.aprobado
    End Function

    Private Function modificar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim modificacion As String = ""
        Dim transporte As Integer

        If ckbTransporte.Checked = True Then
            transporte = 1
        Else
            transporte = 0
        End If
        conexion.ConnectionString = cadena
        conexion.Open()

        modificacion = "UPDATE Proveedores"
        modificacion &= " Set razon_social='" & Me.txtRazonSocial.Text & "'"
        modificacion &= ", CUIT='" & Me.txtCUIT.Text & "'"
        modificacion &= ", direccion='" & Me.txtDireccion.Text & "'"
        modificacion &= ", barrio='" & Me.txtBarrio.Text & "'"
        modificacion &= ", localidad='" & Me.txtLocalidad.Text & "'"
        modificacion &= ", telefono=" & Me.txtTelefono.Text
        modificacion &= ", es_transporte=" & transporte
        modificacion &= ", tipo_factura=" & Me.cmbTipoFactura.SelectedValue
        modificacion &= ", mail='" & Me.txtMail.Text & "'"
        modificacion &= " WHERE (razon_social='" & Me.txtRazonSocial.Text & "')"

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = modificacion

        cmd.ExecuteNonQuery()
        conexion.Close()

        Return termino.aprobado

    End Function

    Private Sub btnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscar.Click, btnEliminar.Click
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM Proveedores WHERE razon_social LIKE '" & txtRazonSocial.Text & "'"

        tabla.Load(cmd.ExecuteReader())
        If tabla.Rows.Count = 1 Then


            Me.txtNombreProveedor.Text = tabla.Rows(0)("numero")
            Me.txtBarrio.Text = tabla.Rows(0)("barrio")
            Me.txtCUIT.Text = tabla.Rows(0)("CUIT")
            Me.txtDireccion.Text = tabla.Rows(0)("direccion")
            Me.txtLocalidad.Text = tabla.Rows(0)("localidad")
            Me.txtFechaAlta.Text = tabla.Rows(0)("fecha_alta")
            Me.txtRazonSocial.Text = tabla.Rows(0)("razon_social")
            Me.txtTelefono.Text = tabla.Rows(0)("telefono")
            Me.txtMail.Text = tabla.Rows(0)("mail")
            Me.cmbTipoFactura.SelectedValue = tabla.Rows(0)("tipo_factura")
            Me.txtMontoMinimo.Text = tabla.Rows(0)("monto_minimo")
            Me.txtFax.Text = tabla.Rows(0)("fax")
            Me.txtNumero.Text = tabla.Rows(0)("numero")

            Me.txtNombreContacto.Text = tabla.Rows(0)("nombre_contacto")
            Me.txtApellidoContacto.Text = tabla.Rows(0)("apellido_contacto")
            Me.txtCelular.Text = tabla.Rows(0)("celular_contacto")
        Else
            MsgBox("No se encontro el proveedor", MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Aviso")
        End If

        conexion.Close()
    End Sub

    Private Sub btnSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalir.Click
        Me.Close()

    End Sub

    Private Sub proveedores_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If (MsgBox("¿Desea salir?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Atencion") = MsgBoxResult.Yes) Then
            e.Cancel = False
        Else
            e.Cancel = True


        End If
    End Sub

    Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE FROM Proveedores WHERE numero = " & txtNumero.Text
        cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub

    Private Sub btnNuevoConctacto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        txtNombreContacto.Enabled = True
        txtApellidoContacto.Enabled = True
        txtCelular.Enabled = True
    End Sub

    Private Sub btnGuardarContacto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim modificacion As String = ""

        conexion.ConnectionString = cadena
        conexion.Open()

        modificacion = "UPDATE Proveedores"
        modificacion &= " Set nombre_contacto='" & Me.txtNombreContacto.Text & "'"
        modificacion &= ", apellido_contacto='" & Me.txtApellidoContacto.Text & "'"
        modificacion &= ", celular_contacto='" & Me.txtCelular.Text & "'"
        modificacion &= " WHERE (numero='" & Me.txtNumero.Text & "')"

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = modificacion

        cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub

    Private Function cargarMayor() As Integer
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim max As Integer

        conexion.ConnectionString = cadena
        conexion.Open()

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT MAX(numero) as Legajo FROM Proveedores"
        max = cmd.ExecuteScalar
        conexion.Close()

        Return max + 1
    End Function



    Private Sub txtNombreProveedor_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNombreProveedor.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtNombreProveedor.MaxLength = 25
    End Sub

    Private Sub txtNumero_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNumero.TextChanged

    End Sub

    Private Sub txtRazonSocial_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRazonSocial.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtRazonSocial.MaxLength = 30
    End Sub

    Private Sub txtTelefono_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelefono.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 45 Then e.Handled = True
        txtRazonSocial.MaxLength = 30
    End Sub

    Private Sub txtDireccion_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDireccion.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True

    End Sub

    Private Sub txtFax_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtFax.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
    End Sub


    Private Sub txtBarrio_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtBarrio.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtBarrio.MaxLength = 30
    End Sub

    Private Sub txtMail_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtMail.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 64 Then e.Handled = True
        txtMail.MaxLength = 30
    End Sub


    Private Sub txtLocalidad_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtLocalidad.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
    End Sub

    Private Sub txtHorario_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtHorario.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 64 Then e.Handled = True
        txtHorario.MaxLength = 30
    End Sub

    Private Sub txtMontoMinimo_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtMontoMinimo.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 46 Then e.Handled = True
        txtMontoMinimo.MaxLength = 18
    End Sub

    Private Sub txtNombreContacto_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNombreContacto.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtNombreContacto.MaxLength = 25
    End Sub

    Private Sub txtApellidoContacto_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtApellidoContacto.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 Then e.Handled = True
        txtApellidoContacto.MaxLength = 25
    End Sub

    
    Private Sub txtCelular_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtCelular.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack And Asc(e.KeyChar) <> 32 And Asc(e.KeyChar) <> 45 Then e.Handled = True
        txtCelular.MaxLength = 30
    End Sub

    Private Sub btnCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelar.Click
        resetData()
        btnBuscar.Enabled = True
        btnEditar.Enabled = True
        btnEliminar.Enabled = True
        btnGuardar.Enabled = False

    End Sub

    Private Sub resetData()

        txtRazonSocial.Text = ""
        txtNombreProveedor.Text = ""
        txtNombreContacto.Text = ""
        txtApellidoContacto.Text = ""
        txtCelular.Text = ""
        txtCUIT.Text = ""
        txtBarrio.Text = ""
        txtDireccion.Text = ""
        txtNumero.Text = ""
        txtLocalidad.Text = ""
        txtTelefono.Text = ""
        txtHorario.Text = ""
        txtMail.Text = ""
        txtFax.Text = ""
        txtMontoMinimo.Text = ""
        cmbTipoFactura.SelectedIndex = -1


        txtNombreProveedor.Enabled = False
        txtNombreContacto.Enabled = False
        txtApellidoContacto.Enabled = False
        txtCelular.Enabled = False
        txtCUIT.Enabled = False
        txtBarrio.Enabled = False
        txtDireccion.Enabled = False
        txtNumero.Enabled = False
        txtLocalidad.Enabled = False
        txtTelefono.Enabled = False
        txtHorario.Enabled = False
        txtMail.Enabled = False
        txtFax.Enabled = False
        txtMontoMinimo.Enabled = False
        cmbTipoFactura.Enabled = False


    End Sub

    Private Sub btnEditar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        btnNuevo.Enabled = False
        btnEliminar.Enabled = False
        btnCancelar.Enabled = True
        btnSalir.Enabled = True

        txtNombreProveedor.Enabled = False
        txtNombreContacto.Enabled = True
        txtApellidoContacto.Enabled = True
        txtCelular.Enabled = True
        txtCUIT.Enabled = False
        txtBarrio.Enabled = True
        txtDireccion.Enabled = True
        txtNumero.Enabled = True
        txtLocalidad.Enabled = True
        txtTelefono.Enabled = True
        txtHorario.Enabled = True
        txtMail.Enabled = True
        txtFax.Enabled = True
        txtMontoMinimo.Enabled = True
        txtRazonSocial.Enabled = False
        cmbTipoFactura.Enabled = True
        txtNumero.Enabled = False
    End Sub
End Class